﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using NewLife;
using NewLife.Caching;
using NewLife.Collections;
using NewLife.Data;

namespace XCode.DataAccessLayer;

partial class DAL
{
    #region 属性
    [ThreadStatic]
    private static Int32 _QueryTimes;
    /// <summary>查询次数</summary>
    public static Int32 QueryTimes => _QueryTimes;

    [ThreadStatic]
    private static Int32 _ExecuteTimes;
    /// <summary>执行次数</summary>
    public static Int32 ExecuteTimes => _ExecuteTimes;

    /// <summary>只读实例。读写分离时，读取操作分走</summary>
    public DAL ReadOnly { get; set; }

    /// <summary>读写分离策略。忽略时间区间和表名</summary>
    public ReadWriteStrategy Strategy { get; set; } = new ReadWriteStrategy();
    #endregion

    #region 数据操作方法
    /// <summary>根据条件把普通查询SQL格式化为分页SQL。</summary>
    /// <param name="builder">查询生成器</param>
    /// <param name="startRowIndex">开始行，0表示第一行</param>
    /// <param name="maximumRows">最大返回行数，0表示所有行</param>
    /// <returns>分页SQL</returns>
    public SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
    {
        if (startRowIndex <= 0 && maximumRows <= 0) return builder;

        // 2016年7月2日 HUIYUE 取消分页SQL缓存，此部分缓存提升性能不多，但有可能会造成分页数据不准确，感觉得不偿失
        return Db.PageSplit(builder.Clone(), startRowIndex, maximumRows);
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <returns></returns>
    public DataSet Select(String sql)
    {
        return QueryByCache(sql, "", "", (s, k2, k3) => Session.Query(s), nameof(Select));
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="builder">SQL语句</param>
    /// <param name="startRowIndex">开始行，0表示第一行</param>
    /// <param name="maximumRows">最大返回行数，0表示所有行</param>
    /// <returns></returns>
    public DataSet Select(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
    {
        return QueryByCache(builder, startRowIndex, maximumRows, (sb, start, max) =>
        {
            sb = PageSplit(sb, start, max);
            return Session.Query(sb.ToString(), CommandType.Text, sb.Parameters.ToArray());
        }, nameof(Select));
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="builder">SQL语句</param>
    /// <param name="startRowIndex">开始行，0表示第一行</param>
    /// <param name="maximumRows">最大返回行数，0表示所有行</param>
    /// <returns></returns>
    public DbTable Query(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
    {
        return QueryByCache(builder, startRowIndex, maximumRows, (sb, start, max) =>
        {
            sb = PageSplit(sb, start, max);
            return Session.Query(sb.ToString(), sb.Parameters.ToArray());
        }, nameof(Query));
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public DbTable Query(String sql, IDictionary<String, Object> ps = null)
    {
        return QueryByCache(sql, ps, "", (s, p, k3) => Session.Query(s, Db.CreateParameters(p)), nameof(Query));
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="sb">查询生成器</param>
    /// <returns></returns>
    public Int32 SelectCount(SelectBuilder sb)
    {
        return (Int32)QueryByCache(sb, "", "", (s, k2, k3) => Session.QueryCount(s), nameof(SelectCount));
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Int32 SelectCount(String sql, CommandType type, params IDataParameter[] ps)
    {
        return (Int32)QueryByCache(sql, type, ps, (s, t, p) => Session.QueryCount(s, t, p), nameof(SelectCount));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <returns></returns>
    public Int32 Execute(String sql)
    {
        return ExecuteByCache(sql, "", "", (s, t, p) => Session.Execute(s));
    }

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql"></param>
    /// <returns>新增行的自动编号</returns>
    public Int64 InsertAndGetIdentity(String sql)
    {
        return ExecuteByCache(sql, "", "", (s, t, p) => Session.InsertAndGetIdentity(s));
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public DataSet Select(String sql, CommandType type, params IDataParameter[] ps)
    {
        return QueryByCache(sql, type, ps, (s, t, p) => Session.Query(s, t, p), nameof(Select));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Int32 Execute(String sql, CommandType type, params IDataParameter[] ps)
    {
        return ExecuteByCache(sql, type, ps, (s, t, p) => Session.Execute(s, t, p));
    }

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql"></param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns>新增行的自动编号</returns>
    public Int64 InsertAndGetIdentity(String sql, CommandType type, params IDataParameter[] ps)
    {
        return ExecuteByCache(sql, type, ps, (s, t, p) => Session.InsertAndGetIdentity(s, t, p));
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public DataSet Select(String sql, CommandType type, IDictionary<String, Object> ps)
    {
        return QueryByCache(sql, type, ps, (s, t, p) => Session.Query(s, t, Db.CreateParameters(p)), nameof(Select));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Int32 Execute(String sql, CommandType type, IDictionary<String, Object> ps)
    {
        return ExecuteByCache(sql, type, ps, (s, t, p) => Session.Execute(s, t, Db.CreateParameters(p)));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="commandTimeout">命令超时时间，一般用于需要长时间执行的命令</param>
    /// <returns></returns>
    public Int32 Execute(String sql, Int32 commandTimeout)
    {
        return ExecuteByCache(sql, commandTimeout, "", (s, t, p) =>
        {
            using var cmd = Session.CreateCommand(s);
            if (t > 0) cmd.CommandTimeout = t;
            return Session.Execute(cmd);
        });
    }

    /// <summary>执行SQL语句，返回结果中的第一行第一列</summary>
    /// <typeparam name="T">返回类型</typeparam>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public T ExecuteScalar<T>(String sql, CommandType type, IDictionary<String, Object> ps)
    {
        return ExecuteByCache(sql, type, ps, (s, t, p) => Session.ExecuteScalar<T>(s, t, Db.CreateParameters(p)));
    }
    #endregion

    #region 异步操作
    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="builder">SQL语句</param>
    /// <param name="startRowIndex">开始行，0表示第一行</param>
    /// <param name="maximumRows">最大返回行数，0表示所有行</param>
    /// <returns></returns>
    public Task<DbTable> QueryAsync(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
    {
        return QueryByCacheAsync(builder, startRowIndex, maximumRows, (sb, start, max) =>
        {
            sb = PageSplit(sb, start, max);
            return Session.QueryAsync(sb.ToString(), sb.Parameters.ToArray());
        }, nameof(QueryAsync));
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Task<DbTable> QueryAsync(String sql, IDictionary<String, Object> ps = null)
    {
        return QueryByCacheAsync(sql, ps, "", (s, p, k3) => Session.QueryAsync(s, Db.CreateParameters(p)), nameof(QueryAsync));
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="sb">查询生成器</param>
    /// <returns></returns>
    public Task<Int64> SelectCountAsync(SelectBuilder sb)
    {
        return QueryByCacheAsync(sb, "", "", (s, k2, k3) => Session.QueryCountAsync(s), nameof(SelectCountAsync));
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Task<Int64> SelectCountAsync(String sql, CommandType type, params IDataParameter[] ps)
    {
        return QueryByCacheAsync(sql, type, ps, (s, t, p) => Session.QueryCountAsync(s, t, p), nameof(SelectCountAsync));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <returns></returns>
    public Task<Int32> ExecuteAsync(String sql)
    {
        return ExecuteByCacheAsync(sql, "", "", (s, t, p) => Session.ExecuteAsync(s));
    }

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql"></param>
    /// <returns>新增行的自动编号</returns>
    public Task<Int64> InsertAndGetIdentityAsync(String sql)
    {
        return ExecuteByCacheAsync(sql, "", "", (s, t, p) => Session.InsertAndGetIdentityAsync(s));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Task<Int32> ExecuteAsync(String sql, CommandType type, params IDataParameter[] ps)
    {
        return ExecuteByCacheAsync(sql, type, ps, (s, t, p) => Session.ExecuteAsync(s, t, p));
    }

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql"></param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns>新增行的自动编号</returns>
    public Task<Int64> InsertAndGetIdentityAsync(String sql, CommandType type, params IDataParameter[] ps)
    {
        return ExecuteByCacheAsync(sql, type, ps, (s, t, p) => Session.InsertAndGetIdentityAsync(s, t, p));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Task<Int32> ExecuteAsync(String sql, CommandType type, IDictionary<String, Object> ps)
    {
        return ExecuteByCacheAsync(sql, type, ps, (s, t, p) => Session.ExecuteAsync(s, t, Db.CreateParameters(p)));
    }

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="commandTimeout">命令超时时间，一般用于需要长时间执行的命令</param>
    /// <returns></returns>
    public Task<Int32> ExecuteAsync(String sql, Int32 commandTimeout)
    {
        return ExecuteByCacheAsync(sql, commandTimeout, "", (s, t, p) =>
        {
            using var cmd = Session.CreateCommand(s);
            if (t > 0) cmd.CommandTimeout = t;
            return Session.ExecuteAsync(cmd);
        });
    }

    /// <summary>执行SQL语句，返回结果中的第一行第一列</summary>
    /// <typeparam name="T">返回类型</typeparam>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public Task<T> ExecuteScalarAsync<T>(String sql, CommandType type, IDictionary<String, Object> ps)
    {
        return ExecuteByCacheAsync(sql, type, ps, (s, t, p) => Session.ExecuteScalarAsync<T>(s, t, Db.CreateParameters(p)));
    }
    #endregion

    #region 事务
    /// <summary>开始事务</summary>
    /// <remarks>
    /// Read Uncommitted: 允许读取脏数据，一个事务能看到另一个事务还没有提交的数据。（不会阻止其它操作）
    /// Read Committed: 确保事务读取的数据都必须是已经提交的数据。它限制了读取中间的，没有提交的，脏的数据。
    /// 但是它不能确保当事务重新去读取的时候，读的数据跟上次读的数据是一样的，也就是说当事务第一次读取完数据后，
    /// 该数据是可能被其他事务修改的，当它再去读取的时候，数据可能是不一样的。（数据隐藏，不阻止）
    /// Repeatable Read: 是一个更高级别的隔离级别，如果事务再去读取同样的数据，先前的数据是没有被修改过的。（阻止其它修改）
    /// Serializable: 它做出了最有力的保证，除了每次读取的数据是一样的，它还确保每次读取没有新的数据。（阻止其它添删改）
    /// </remarks>
    /// <param name="level">事务隔离等级</param>
    /// <returns>剩下的事务计数</returns>
    public Int32 BeginTransaction(IsolationLevel level = IsolationLevel.ReadCommitted)
    {
        //CheckDatabase();

        return Session.BeginTransaction(level);
    }

    /// <summary>提交事务</summary>
    /// <returns>剩下的事务计数</returns>
    public Int32 Commit() => Session.Commit();

    /// <summary>回滚事务，忽略异常</summary>
    /// <returns>剩下的事务计数</returns>
    public Int32 Rollback() => Session.Rollback();
    #endregion

    #region 缓存
    /// <summary>缓存存储</summary>
    public ICache Store { get; set; }

    /// <summary>数据层缓存。默认10秒</summary>
    public Int32 Expire { get; set; }

    private static readonly AsyncLocal<String> _SpanTag = new();

    /// <summary>埋点上下文信息。用于附加在埋点标签后的上下文信息</summary>
    public static void SetSpanTag(String value) => _SpanTag.Value = value;

    private ICache GetCache()
    {
        var st = Store;
        if (st != null) return st;

        var exp = Expire;
        if (exp == 0) exp = Db.DataCache;
        if (exp == 0) exp = Setting.Current.DataCacheExpire;
        if (exp <= 0) return null;

        Expire = exp;

        lock (this)
        {
            if (Store == null)
            {
                var p = exp / 2;
                if (p < 30) p = 30;

                st = Store = new MemoryCache { Period = p, Expire = exp };
            }
        }

        return st;
    }

    private TResult QueryByCache<T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func<T1, T2, T3, TResult> callback, String action)
    {
        // 读写分离
        if (Strategy != null)
        {
            if (Strategy.Validate(this, k1 + "", action)) return ReadOnly.QueryByCache(k1, k2, k3, callback, action);
        }

        //CheckDatabase();

        // 读缓存
        var cache = GetCache();
        var key = "";
        if (cache != null)
        {
            var sb = Pool.StringBuilder.Get();
            if (!action.IsNullOrEmpty())
            {
                sb.Append(action);
                sb.Append('#');
            }
            Append(sb, k1);
            Append(sb, k2);
            Append(sb, k3);
            key = sb.Put(true);

            if (cache.TryGetValue<TResult>(key, out var value)) return value;
        }

        Interlocked.Increment(ref _QueryTimes);
        var rs = Invoke(k1, k2, k3, callback, action);

        cache?.Set(key, rs, Expire);

        return rs;
    }

    private TResult ExecuteByCache<T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func<T1, T2, T3, TResult> callback)
    {
        if (Db.Readonly) throw new InvalidOperationException($"数据连接[{ConnName}]只读，禁止执行{k1}");

        //CheckDatabase();

        var rs = Invoke(k1, k2, k3, callback, "Execute");

        GetCache()?.Clear();

        Interlocked.Increment(ref _ExecuteTimes);

        return rs;
    }

    private TResult Invoke<T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func<T1, T2, T3, TResult> callback, String action)
    {
        var tracer = Tracer ?? GlobalTracer;
        var traceName = "";
        var sql = "";

        // 从sql解析表名，作为跟踪名一部分。正则避免from前后换行的情况
        if (tracer != null)
        {
            sql = (k1 + "").Trim();
            if (action == "Execute")
            {
                // 使用 Insert/Update/Delete 作为埋点操作名
                var p = sql.IndexOf(' ');
                if (p > 0) action = sql[..p];
            }
            else if (action.EqualIgnoreCase("Query", "Select"))
            {
                // 查询数据时，Group作为独立埋点操作名
                if (sql.ToLower().Contains("group by"))
                    action = "Group";
            }

            traceName = $"db:{ConnName}:{action}";

            var tables = GetTables(sql);
            if (tables.Length > 0) traceName += ":" + tables.Join("-");
        }

        // 使用k1参数作为tag，一般是sql
        var span = tracer?.NewSpan(traceName, sql);
        try
        {
            var rs = callback(k1, k2, k3);

            if (span != null)
            {
                if (rs is DbTable dt)
                    span.Tag = $"{sql} [rows={dt.Rows?.Count}]";
                else if (rs is DataSet ds && ds.Tables.Count > 0)
                    span.Tag = $"{sql} [rows={ds.Tables[0].Rows.Count}]";
                else
                    span.Tag = $"{sql} [result={rs}]";

                var stag = _SpanTag.Value;
                if (!stag.IsNullOrEmpty()) span.Tag += " " + stag;
            }

            return rs;
        }
        catch (Exception ex)
        {
            span?.SetError(ex, null);
            throw;
        }
        finally
        {
            span?.Dispose();
        }
    }

    private async Task<TResult> QueryByCacheAsync<T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func<T1, T2, T3, Task<TResult>> callback, String action)
    {
        // 读写分离
        if (Strategy != null)
        {
            if (Strategy.Validate(this, k1 + "", action)) return await ReadOnly.QueryByCacheAsync(k1, k2, k3, callback, action);
        }

        //CheckDatabase();

        // 读缓存
        var cache = GetCache();
        var key = "";
        if (cache != null)
        {
            var sb = Pool.StringBuilder.Get();
            if (!action.IsNullOrEmpty())
            {
                sb.Append(action);
                sb.Append('#');
            }
            Append(sb, k1);
            Append(sb, k2);
            Append(sb, k3);
            key = sb.Put(true);

            if (cache.TryGetValue<TResult>(key, out var value)) return value;
        }

        Interlocked.Increment(ref _QueryTimes);
        var rs = await InvokeAsync(k1, k2, k3, callback, action);

        cache?.Set(key, rs, Expire);

        return rs;
    }

    private async Task<TResult> ExecuteByCacheAsync<T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func<T1, T2, T3, Task<TResult>> callback)
    {
        if (Db.Readonly) throw new InvalidOperationException($"数据连接[{ConnName}]只读，禁止执行{k1}");

        //CheckDatabase();

        var rs = await InvokeAsync(k1, k2, k3, callback, "Execute");

        GetCache()?.Clear();

        Interlocked.Increment(ref _ExecuteTimes);

        return rs;
    }

    private async Task<TResult> InvokeAsync<T1, T2, T3, TResult>(T1 k1, T2 k2, T3 k3, Func<T1, T2, T3, Task<TResult>> callback, String action)
    {
        var tracer = Tracer ?? GlobalTracer;
        var traceName = "";
        var sql = "";

        // 从sql解析表名，作为跟踪名一部分。正则避免from前后换行的情况
        if (tracer != null)
        {
            sql = (k1 + "").Trim();
            if (action == "Execute")
            {
                // 使用 Insert/Update/Delete 作为埋点操作名
                var p = sql.IndexOf(' ');
                if (p > 0) action = sql[..p];
            }

            traceName = $"db:{ConnName}:{action}";

            var tables = GetTables(sql);
            if (tables.Length > 0) traceName += ":" + tables.Join("-");
        }

        // 使用k1参数作为tag，一般是sql
        var span = tracer?.NewSpan(traceName, sql);
        try
        {
            var rs = await callback(k1, k2, k3);

            if (span != null)
            {
                if (rs is DbTable dt)
                    span.Tag = $"{sql} [rows={dt.Rows?.Count}]";
                else if (rs is DataSet ds && ds.Tables.Count > 0)
                    span.Tag = $"{sql} [rows={ds.Tables[0].Rows.Count}]";
                else
                    span.Tag = $"{sql} [result={rs}]";

                var stag = _SpanTag.Value;
                if (!stag.IsNullOrEmpty()) span.Tag += " " + stag;
            }

            return rs;
        }
        catch (Exception ex)
        {
            span?.SetError(ex, null);
            throw;
        }
        finally
        {
            span?.Dispose();
        }
    }

    private static readonly Regex reg_table = new("(?:\\s+from|insert\\s+into|update|\\s+join|drop\\s+table|truncate\\s+table)\\s+[`'\"\\[]?([\\w]+)[`'\"\\[]?", RegexOptions.Compiled | RegexOptions.IgnoreCase);
    /// <summary>从Sql语句中截取表名</summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static String[] GetTables(String sql)
    {
        var list = new List<String>();
        var ms = reg_table.Matches(sql);
        foreach (Match item in ms)
        {
            list.Add(item.Groups[1].Value);
        }
        return list.ToArray();
    }

    private static void Append(StringBuilder sb, Object value)
    {
        if (value == null) return;

        if (value is SelectBuilder builder)
        {
            sb.Append(builder);
            foreach (var item in builder.Parameters)
            {
                sb.Append('#');
                sb.Append(item.ParameterName);
                sb.Append('#');
                sb.Append(item.Value);
            }
        }
        else if (value is IDataParameter[] ps)
        {
            foreach (var item in ps)
            {
                sb.Append('#');
                sb.Append(item.ParameterName);
                sb.Append('#');
                sb.Append(item.Value);
            }
        }
        else if (value is IDictionary<String, Object> dic)
        {
            foreach (var item in dic)
            {
                sb.Append('#');
                sb.Append(item.Key);
                sb.Append('#');
                sb.Append(item.Value);
            }
        }
        else
        {
            sb.Append('#');
            sb.Append(value);
        }
    }
    #endregion
}